﻿CREATE VIEW [dbo].[vwAudit_ServerRoleMembers]
AS

SELECT
	t3.[app_name]
	,t3.[app_contact]
	,t2.[ssd_server_name]
	,t2.[ssd_sqlserver_name_ro]
	,[srm_databasename]
	,[srm_role_name]
	,[srm_member_name]
	,(CASE [srm_member_type]
		WHEN 'A' THEN 'Application Role'
		WHEN 'C' THEN 'User mapped to a certificate'
		WHEN 'E' THEN 'External user from Azure Active Directory'
		WHEN 'G' THEN 'Windows group'
		WHEN 'K' THEN 'User mapped to an asymmetric key'
		WHEN 'R' THEN 'Database role'
		WHEN 'S' THEN 'SQL user'
		WHEN 'U' THEN 'Windows user'
		WHEN 'X' THEN 'External group from Azure Active Directory group or applications'
		ELSE NULL
		END) [srm_member_type]
	,[srm_member_disabled]
	,[srm_start_date]
	,[srm_end_date]
FROM [dbo].[tblWH_ServerRoleMembers] t1
JOIN [dbo].[tblMSX_server_discovery] t2
	ON t1.[srm_server_ssd_id] = t2.[ssd_id]
	AND t2.[ssd_is_disabled] = 0
JOIN [dbo].[tblMSX_application] t3 ON t2.[ssd_app_id] = t3.[app_id]